<?php

/**
 * 数据库MySQLI返回类
 *
 * @author      jonwang(jonwang@myqee.com)
 * @category    MyQEE
 * @package     System
 * @subpackage  Core
 * @copyright   Copyright (c) 2008-2011 myqee.com
 * @license     http://www.myqee.com/license.html
 */
class MyQEE_Database_Driver_MySQLI extends Database_Driver
{
    /**
     * 记录当前连接所对应的数据库
     * @var array
     */
    protected static $_current_databases = array();

    /**
     * 记录当前数据库所对应的页面编码
     * @var array
     */
    protected static $_current_charset = array();

    // MySQL uses a backtick for identifiers
    protected $_identifier = '`';

    /**
     * 链接寄存器
     * @var array
     */
    protected static $_connection_instance = array();

    /**
     * 记录连接对象数目，当对象销毁到只有1个时，才关闭对应连接
     * @var array
     */
    protected static $_connection_num = array();

    /**
     * 连接数据库
     * @param boolean $use_master 是否使用主数据库
     * @param boolean $change_database 是否切换到当前库
     */
    public function connect($use_master = true)
    {
        $this->_connect($use_master,$change_database);

        static $last_run_time = 0;
        if ( $last_run_time > 0 && time() - $last_run_time >= 5 )
        {
            # 5秒检测1次
            # 防止因长时间不链接而导致连接丢失的问题 MySQL server has gone away
            static $error_num = 0;
            try
            {
                $ping_status = mysqli_ping($this->_connection);
            }
            catch ( Exception $e )
            {
                $error_num++;
                $ping_status = false;
            }
            if ( ! $ping_status )
            {
                if ( $error_num<5 )
                {
                    $this->close_connect();
                    usleep(100);
                    $this->connect($use_master);
                    $error_num = 0;
                }
                else
                {
                    throw new Exception('connect mysqli server error');
                }
            }
        }
        $last_run_time = time();
    }

    protected function _connect($use_master)
    {
        $database = $hostname = $port = $socket = $username = $password = $persistent = null;
        // Extract the connection parameters, adding required variabels
        extract($this->config['connection']);
        if (!$port>0)
        {
            $port = 3306;
        }

        # 切换主从服务器
        if ( $use_master )
        {
            $type_str = 'master';
        }
        else
        {
            $type_str = 'slaver';
        }
        $_connection_name = '_connection_'.$type_str;
        $_connection_id_name = '_connection_'.$type_str.'_id';
        $c_id = $this->$_connection_id_name;
        if ( $c_id && Database_Driver_MySQLI::$_connection_instance[$c_id] )
        {
            if ( $this->_connection !== Database_Driver_MySQLI::$_connection_instance[$c_id] )
            {
                $this->_connection = Database_Driver_MySQLI::$_connection_instance[$c_id];
                $this->_connection_id = $c_id;
            }
            if ( !empty($this->config['charset']) )
            {
                # 设置编码
                $this->set_charset($this->config['charset']);
            }
            # 选择表
            $this->_select_db($database);
            return;
        }

        # 是否需要去连接
        $need_real_content = true;
        # 检查下是否已经有连接连上去了
        if ( is_array($hostname) && Database_Driver_MySQLI::$_connection_instance )
        {
            if ( $use_master )
            {
                $hostconfig = $hostname['master'];
            }
            else
            {
                $hostconfig = $hostname['slaver'];
            }
            if ( ! is_array($hostconfig) )
            {
                $hostconfig = array($hostconfig);
            }
            foreach ( $hostconfig as $host )
            {
                $_connection_id = $this->_get_connection_hash($host, $port, $username);
                if ( isset(Database_Driver_MySQLI::$_connection_instance[$_connection_id]) )
                {
                    # 设置当前连接
                    $this->$_connection_name = Database_Driver_MySQLI::$_connection_instance[$_connection_id];
                    $need_real_content = false;
                    break;
                }
            }
        }

        if ( $need_real_content )
        {
            $try_num = 5;
            for( $i = 1; $i <= $try_num; $i ++ )
            {
                $hostname = $this->_get_rand_host($use_master);
                $_connection_id = $this->_get_connection_hash($hostname, $port, $username);

                # 尝试重连
                try
                {
                    $time = microtime(true);
                    if ( empty($persistent) )
                    {
                        Database_Driver_MySQLI::$_connection_instance[$_connection_id] = mysqli_init();
                        mysqli_options( Database_Driver_MySQLI::$_connection_instance[$_connection_id] , MYSQLI_OPT_CONNECT_TIMEOUT, 3);
                        mysqli_real_connect(Database_Driver_MySQLI::$_connection_instance[$_connection_id], $hostname, $username, $password, $database, $port, null, MYSQLI_CLIENT_COMPRESS);
                    }
                    else
                    {
                        Database_Driver_MySQLI::$_connection_instance[$_connection_id] = new mysqli($hostname, $username, $password, $database, $port);
                    }

                    Core::debug()->info($hostname . ' mysql connection time:' . (microtime(true) - $time));

                    $this->$_connection_name = Database_Driver_MySQLI::$_connection_instance[$_connection_id];
                    Database_Driver_MySQLI::$_current_databases[$_connection_id] = $database;
                    break;
                }
                catch ( Exception $e )
                {
                    // No connection exists
                    $this->$_connection_name = $this->_connection = null;

                    if ( $i == $try_num )
                    {
                        throw $e;
                    }
                }
                if ( $i == $try_num && ! $this->$_connection_name )
                {
                    throw new Exception('数据库链接失败');
                }
            }
        }

        # 记录链接ID
        $this->_connection_id = $this->$_connection_id_name = $_connection_id;

        # 切换当前链接
        $this->_connection = $this->$_connection_name;

        # 连接使用数加1
        if ( ! isset(Database_Driver_MySQLI::$_connection_num[$_connection_id]) )
        {
            Database_Driver_MySQLI::$_connection_num[$_connection_id] = 1;
        }
        else
        {
            Database_Driver_MySQLI::$_connection_num[$_connection_id] ++;
        }

        if ( ! empty($this->config['charset']) )
        {
            // Set the character set
            $this->set_charset($this->config['charset']);
        }

        #选择数据库
        $this->_select_db($database);
    }

    /**
     * 关闭链接
     */
    public function close_connect()
    {
        if ( null === $this->_connection_id )
        {
            return;
        }
        Database_Driver_MySQLI::$_connection_num[$this->_connection_id] --;
        # 如果主链接使用数<=0，则关闭主链接
        # 注意，当没有采用主从时，$this->_connection_id==$this->_connection_master_id的，所以上面--，则相当于Database::$_connection_num[$this->_connection_master_id]--
        if ( Database_Driver_MySQLI::$_connection_num[$this->_connection_master_id] <= 0 && is_object($this->_connection_master) )
        {
            @mysql_close($this->_connection_master);
            $this->_connection_master = null;
        }

        if ( $this->_connection_master_id != $this->_connection_slaver_id )
        {
            # 主连接与从连接不相同才需要处理
            Database_Driver_MySQLI::$_connection_num[$this->_connection_slaver_id] --;
            # 关闭从链接
            if ( Database_Driver_MySQLI::$_connection_num[$this->_connection_slaver_id] <= 0 && is_object($this->_connection_slaver) )
            {
                @mysqli_close($this->_connection_slaver);
                $this->_connection_slaver = null;
            }
        }

        $this->_connection = null;
        $this->_connection_id = null;
    }

    /**
     * Select the database
     *
     * @param   string Database
     * @return  void
     */
    protected function _select_db($database)
    {
        if ( ! $database ) return;
        if ( ! isset(Database_Driver_MySQLI::$_current_databases[$this->_connection_id]) || $database != Database_Driver_MySQLI::$_current_databases[$this->_connection_id] )
        {
            if ( ! mysqli_select_db($this->_connection, $database) )
            {
                throw new Exception('选择数据表错误:' . mysqli_error($this->_connection), mysqli_errno($this->_connection));
            }

            Database_Driver_MySQLI::$_current_databases[$this->_connection_id] = $database;
        }
    }

    /**
     * 构建SQL语句
     */
    public function compile($builder, $type = 'selete')
    {
        if ( $type == 'selete' )
        {
            return $this->_compile_selete($builder);
        }
        else if ( $type == 'insert' )
        {
            return $this->_compile_insert($builder);
        }
        elseif ( $type == 'replace' )
        {
            return $this->_compile_insert($builder, 'REPLACE');
        }
        elseif ( $type == 'update' )
        {
            return $this->_compile_update($builder);
        }
        elseif ( $type == 'delete' )
        {
            return $this->_compile_delete($builder);
        }
        else
        {
            return $this->_compile_selete($builder);
        }
    }

    public function set_charset($charset)
    {
        // Make sure the database is connected
        $this->_connection or $this->connect();

        static $_set_names = null;
        if ( null === $_set_names )
        {
            // Determine if we can use mysql_set_charset(), which is only
            // available on PHP 5.2.3+ when compiled against MySQL 5.0+
            $_set_names = ! function_exists('mysqli_set_charset');
        }

        if ( isset(Database_Driver_MySQLI::$_current_charset[$this->_connection_id]) && $charset == Database_Driver_MySQLI::$_current_charset[$this->_connection_id] )
        {
            return true;
        }

        if ( $_set_names === true )
        {
            // PHP is compiled against MySQL 4.x
            $status = (bool)mysqli_query('SET NAMES ' . $this->quote($charset), $this->_connection);
        }
        else
        {
            // PHP is compiled against MySQL 5.x
            $status = mysqli_set_charset($this->_connection, $charset);
        }

        if ( $status === false )
        {
            throw new Exception('Error:' . mysqli_error($this->_connection), mysqli_errno($this->_connection));
        }

        # 记录当前设置的编码
        Database_Driver_MySQLI::$_current_charset[$this->_connection_id] = $charset;
    }

    public function escape($value)
    {
        // Make sure the database is connected
        $this->_connection or $this->connect();

        if ( ($value = mysqli_real_escape_string($this->_connection, (string)$value)) === FALSE )
        {
            throw new Exception('Error:' . mysqli_errno($this->_connection), mysqli_error($this->_connection));
        }

        // SQL standard is to use single-quotes for all values
        return "'$value'";
    }

    /**
     * 查询
     * @param string $sql 查询语句
     * @param string $as_object 是否返回对象
     * @param boolean $use_master 是否使用主数据库，不设置则自动判断
     * @return Database_Driver_MySQLI_Result
     */
    public function query($sql, $as_object = null, $use_master = null)
    {
        # 处理编码编码
        if ( $this->config['auto_change_charset'] && $this->config['charset'] !='UTF8' )
        {
            # 转换编码编码
            $sql_run = iconv('UTF-8', $this->config['data_charset'], $sql);
        }
        else
        {
            $sql_run = & $sql;
        }

        $sql = trim($sql);
        Core::debug()->info('SQL:' . $sql);
        if ( preg_match('#^([a-z]+)(:? |\n|\r)#i', $sql, $m) )
        {
            $type = strtoupper($m[1]);
        }
        $typeArr = array(
            'SELECT',
            'SHOW',     //显示表
            'EXPLAIN',  //分析
            'DESCRIBE', //显示结结构
            'INSERT',
            'REPLACE',
            'UPDATE',
            'DELETE',
        );
        if ( ! in_array($type, $typeArr) )
        {
            $type = 'MASTER';
        }
        $slaverType = array('SELECT', 'SHOW', 'EXPLAIN');
        if ( $type != 'MASTER' && in_array($type, $slaverType) )
        {
            if ( $use_master )
            {
                $use_master = true;
            }
            else
            {
                $use_master = false;
            }
        }
        else
        {
            $use_master = true;
        }

        $this->connect($use_master);

        if ( ! empty($this->config['profiling']) )
        {
            static $is_sql_debug = null;
            if ( null === $is_sql_debug ) $is_sql_debug = (bool)Core::debug()->profiler('sql')->is_open();
            if ( $is_sql_debug )
            {
                $benchmark = Core::debug()->profiler('sql')->start('Database', $this->config['connection']['username'] . '@' . $this->config['connection']['hostname'] . ($this->config['connection']['port'] && $this->config['connection']['port'] != '3306' ? ':' . $this->config['connection']['port'] : ''));
            }
        }

        static $is_no_cache = null;
        if ( null === $is_no_cache ) $is_no_cache = (bool)Core::debug()->profiler('nocached')->is_open();
        //显示无缓存数据
        if ( $is_no_cache && strtoupper(substr($sql_run, 0, 6)) == 'SELECT' )
        {
            $sql_run = 'SELECT SQL_NO_CACHE' . substr($sql_run, 6);
        }

        if ( ! empty($this->config['connection']['persistent']) and $this->config['connection']['database'] !== Database_Driver_MySQLI::$_current_databases[$this->_connection_id] )
        {
            // Select database on persistent connections
            $this->_select_db($this->config['connection']['database']);
        }

        // Execute the query
        if ( ($result = mysqli_query($this->_connection, $sql_run)) === FALSE )
        {
            if ( isset($benchmark) )
            {
                // This benchmark is worthless
                $benchmark->delete();
            }

            if ( Core::$local_debug )
            {
                $err = 'Error:' . mysqli_error($this->_connection) . '. SQL:' . $sql;
            }
            else
            {
                $err = mysqli_error($this->_connection);
            }
            throw new Exception($err, mysqli_errno($this->_connection));
        }

        if ( isset($benchmark) )
        {
            # 在线查看SQL情况
            if ( Core::debug()->profiler('sql')->is_open() )
            {
                $host = $this->_get_hostname_by_connection_hash($this->_connection_id);
                $data = array();
                $data[0]['db'] = $host['hostname'] . '/' . $this->config['connection']['database'] . '/';
                $data[0]['select_type'] = '';
                $data[0]['table'] = '';
                $data[0]['key'] = '';
                $data[0]['key_len'] = '';
                $data[0]['Extra'] = '';
                $data[0]['query'] = '';
                $data[0]['type'] = '';
                $data[0]['id'] = '';
                $data[0]['row'] = count($result);
                $data[0]['ref'] = '';
                $data[0]['all rows'] = '';
                $data[0]['possible_keys'] = '';

                if ( strtoupper(substr($sql, 0, 6)) == 'SELECT' )
                {
                    $re = $this->_connection->query('EXPLAIN ' . $sql);
                    $i = 0;
                    while ( true == ($row = $re->fetch_array(MYSQLI_NUM)) )
                    {
                        $data[$i]['id'] = $row[0];
                        $data[$i]['select_type'] = $row[1];
                        $data[$i]['table'] = $row[2];
                        $data[$i]['type'] = $row[3];
                        $data[$i]['key'] = $row[5];
                        $data[$i]['key_len'] = $row[6];
                        $data[$i]['ref'] = $row[7];
                        $data[$i]['all rows'] = $row[8];
                        $data[$i]['Extra'] = $row[9];
                        if ( $i == 0 ) $data[$i]['query'] = '';
                        $data[$i]['possible_keys'] = $row[4];
                        $i ++;
                    }
                }

                $data[0]['query'] = $sql;
            }
            else
            {
                $data = null;
            }
            Core::debug()->profiler('sql')->stop($data);
        }

        // Set the last query
        $this->last_query = $sql;

        if ( $type === 'INSERT' || $type === 'REPLACE' )
        {
            // Return a list of insert id and rows created
            return array(
                mysqli_insert_id($this->_connection),
                mysqli_affected_rows($this->_connection)
            );
        }
        elseif ( $type === 'UPDATE' || $type === 'DELETE' )
        {
            // Return the number of rows affected
            return mysqli_affected_rows($this->_connection);
        }
        else
        {
            // Return an iterator of results
            return new Database_Driver_MySQLI_Result($result, $sql, $as_object ,$this->config );
        }
    }

    public function datatype($type)
    {
        static $types = array(
            'blob'	                        => array( 'type' => 'string', 'binary' => TRUE, 'character_maximum_length' => '65535' ),
            'bool'	                        => array( 'type' => 'bool' ),
            'bigint unsigned'	            => array( 'type' => 'int', 'min' => '0', 'max' => '18446744073709551615' ),
            'datetime'	                    => array( 'type' => 'string' ),
            'decimal unsigned'	            => array( 'type' => 'float', 'exact' => TRUE, 'min' => '0' ),
            'double'	                    => array( 'type' => 'float' ),
            'double precision unsigned'	    => array( 'type' => 'float', 'min' => '0' ),
            'double unsigned'	            => array( 'type' => 'float', 'min' => '0' ),
            'enum'	                        => array( 'type' => 'string' ),
            'fixed'	                        => array( 'type' => 'float', 'exact' => TRUE ),
            'fixed unsigned'	            => array( 'type' => 'float', 'exact' => TRUE, 'min' => '0' ),
            'float unsigned'	            => array( 'type' => 'float', 'min' => '0' ),
            'int unsigned'	                => array( 'type' => 'int', 'min' => '0', 'max' => '4294967295' ),
            'integer unsigned'	            => array( 'type' => 'int', 'min' => '0', 'max' => '4294967295' ),
            'longblob'	                    => array( 'type' => 'string', 'binary' => TRUE, 'character_maximum_length' => '4294967295' ),
            'longtext'	                    => array( 'type' => 'string', 'character_maximum_length' => '4294967295' ),
            'mediumblob'	                => array( 'type' => 'string', 'binary' => TRUE, 'character_maximum_length' => '16777215' ),
            'mediumint'	                    => array( 'type' => 'int', 'min' => '-8388608', 'max' => '8388607' ),
            'mediumint unsigned'	        => array( 'type' => 'int', 'min' => '0', 'max' => '16777215' ),
            'mediumtext'	                => array( 'type' => 'string', 'character_maximum_length' => '16777215' ),
            'national varchar'	            => array( 'type' => 'string' ),
            'numeric unsigned'	            => array( 'type' => 'float', 'exact' => TRUE, 'min' => '0' ),
            'nvarchar'	                    => array( 'type' => 'string' ),
            'point'	                        => array( 'type' => 'string', 'binary' => TRUE ),
            'real unsigned'	                => array( 'type' => 'float', 'min' => '0' ),
            'set'	                        => array( 'type' => 'string' ),
            'smallint unsigned'	            => array( 'type' => 'int', 'min' => '0', 'max' => '65535' ),
            'text'	                        => array( 'type' => 'string', 'character_maximum_length' => '65535' ),
            'tinyblob'	                    => array( 'type' => 'string', 'binary' => TRUE, 'character_maximum_length' => '255' ),
            'tinyint'	                    => array( 'type' => 'int', 'min' => '-128', 'max' => '127' ),
            'tinyint unsigned'	            => array( 'type' => 'int', 'min' => '0', 'max' => '255' ),
            'tinytext'	                    => array( 'type' => 'string', 'character_maximum_length' => '255' ),
            'year'	                        => array( 'type' => 'string' )
        );

        $type = str_replace(' zerofill', '', $type);

        if ( isset($types[$type]) ) return $types[$type];

        return parent::datatype($type);
    }

    public function quote($value)
    {
        if ( $value === NULL )
        {
            return 'NULL';
        }
        elseif ( $value === TRUE )
        {
            return "'1'";
        }
        elseif ( $value === FALSE )
        {
            return "'0'";
        }
        elseif ( is_object($value) )
        {
            if ( $value instanceof Database )
            {
                // Create a sub-query
                return '(' . $value->compile() . ')';
            }
            elseif ( $value instanceof QueryBuilder_Expression )
            {
                // Use a raw expression
                return $value->value();
            }
            else
            {
                // Convert the object to a string
                return $this->quote((string)$value);
            }
        }
        elseif ( is_array($value) )
        {
            return '(' . implode(', ', array_map(array($this, __FUNCTION__), $value)) . ')';
        }
        elseif ( is_int($value) )
        {
            return (int)$value;
        }
        elseif ( is_float($value) )
        {
            // Convert to non-locale aware float to prevent possible commas
            return sprintf('%F', $value);
        }

        return $this->escape($value);
    }

    /**
     * Quote a database table name and adds the table prefix if needed.
     *
     * $table = $db->quote_table($table);
     *
     * @param   mixed   table name or array(table, alias)
     * @return  string
     * @uses    Database::_quote_identifier
     * @uses    Database::table_prefix
     */
    public function quote_table($value)
    {
        // Assign the table by reference from the value
        if ( is_array($value) )
        {
            $table = & $value[0];
        }
        else
        {
            $table = & $value;
        }

        if ( is_string($table) and strpos($table, '.') === FALSE )
        {
            // Add the table prefix for tables
            $table = $this->config['table_prefix'] . $table;
        }

        return $this->_quote_identifier($value);
    }

    /**
     * 创建一个数据库
     *
     * @param string $database
     * @param string $charset 编码，不传则使用数据库连接配置相同到编码
     * @param string $collate 整理格式
     * @return boolean
     * @throws Exception
     */
    public function create_database( $database, $charset = null, $collate=null )
    {
        $config = $this->config;
        $this->config['connection']['database'] = null;
        if (!$charset)
        {
            $charset = $this->config['charset'];
        }
        $sql = 'CREATE DATABASE '.$this->_quote_identifier($database).' DEFAULT CHARACTER SET '.$charset;
        if ($collate)
        {
            $sql .= ' COLLATE '.$collate;
        }
        try
        {
            $result = $this->query($sql,null,true)->result();
            $this->config = $config;
            return $result;
        }
        catch (Exception $e)
        {
            $this->config = $config;
            throw $e;
        }
    }

    protected function _quote_identifier($value)
    {
        if ( is_string($value) )
        {
            $value = trim($value);
        }

        if ( $value === '*' )
        {
            return $value;
        }
        elseif ( is_object($value) )
        {
            if ( $value instanceof Database )
            {
                // Create a sub-query
                return '(' . $value->compile() . ')';
            }
            elseif ( $value instanceof QueryBuilder_Expression )
            {
                // Use a raw expression
                return $value->value();
            }
            else
            {
                // Convert the object to a string
                return $this->_quote_identifier((string)$value);
            }
        }
        elseif ( is_array($value) || stripos($value, ' AS ') !== FALSE )
        {
            if ( ! is_array($value) )
            {
                $value = str_ireplace(' AS ', ' AS ', $value);
                $value = explode(' AS ', $value);
            }
            // Separate the column and alias
            list ( $value, $alias ) = $value;

            $this->_as_table[] = $alias;
            return $this->_quote_identifier($value) . ' AS ' . $this->_quote_identifier($alias);
        }

        if ( strpos($value, '"') !== FALSE )
        {
            // Quote the column in FUNC("ident") identifiers
            return preg_replace('/"(.+?)"/e', '$this->_quote_identifier("$1")', $value);
        }
        elseif ( strpos($value, '.') !== FALSE )
        {
            // Split the identifier into the individual parts
            $parts = explode('.', $value);

            $prefix = $this->config['table_prefix'];
            if ( $prefix )
            {
                // Get the offset of the table name, 2nd-to-last part
                // This works for databases that can have 3 identifiers (Postgre)
                $offset = count($parts) - 2;

                if ( ! $this->_as_table || ! in_array($parts[$offset], $this->_as_table) )
                {
                    // Add the table prefix to the table name
                    $parts[$offset] = $prefix . $parts[$offset];
                }
            }

            // Quote each of the parts
            return implode('.', array_map(array($this, __FUNCTION__), $parts));
        }
        else
        {
            return $this->_identifier . $value . $this->_identifier;
        }
    }

    protected function _compile_selete($builder)
    {
        // Callback to quote identifiers
        $quote_ident = array($this, '_quote_identifier');

        // Callback to quote tables
        $quote_table = array($this, 'quote_table');

        // Start a selection query
        $query = 'SELECT ';

        if ( $builder['distinct'] === TRUE )
        {
            // Select only unique results
            $query .= 'DISTINCT ';
        }

        if ( empty($builder['select']) )
        {
            // Select all columns
            $query .= '*';
        }
        else
        {
            // Select all columns
            $query .= implode(', ', array_unique(array_map($quote_ident, $builder['select'])));
        }

        if ( ! empty($builder['from']) )
        {
            // Set tables to select from
            $query .= ' FROM ' . implode(', ', array_unique(array_map($quote_table, $builder['from'])));
        }

        if ( ! empty($builder['join']) )
        {
            // Add tables to join
            $query .= ' ' . $this->_compile_join($builder['join']);
        }

        if ( ! empty($builder['where']) )
        {
            // Add selection conditions
            $query .= ' WHERE ' . $this->_compile_conditions($builder['where'], $builder['parameters']);
        }

        if ( ! empty($builder['group_by']) )
        {
            // Add sorting
            $query .= ' GROUP BY ' . implode(', ', array_map($quote_ident, $builder['group_by']));
        }

        if ( ! empty($builder['having']) )
        {
            // Add filtering conditions
            $query .= ' HAVING ' . $this->_compile_conditions($builder['having'], $builder['parameters']);
        }

        if ( ! empty($builder['order_by']) )
        {
            // Add sorting
            $query .= ' ' . $this->_compile_order_by($builder['order_by']);
        }
        elseif ( $builder['where'] )
        {
            # 如果查询中有in查询，采用自动排序方式
            $in_query = null;
            foreach ( $builder['where'] as $item )
            {
                if ( isset($item['AND']) && $item['AND'][1] == 'in' )
                {
                    if ( count($item['AND'][1]) > 1 )
                    {
                        # 大于1项才需要排序
                        $in_query = $item['AND'];
                    }
                    break;
                }
            }
            if ( $in_query )
            {
                $query .= ' ORDER BY FIELD(' . $this->_quote_identifier($in_query[0]) . ', ' . implode(', ', $this->quote($in_query[2])) . ')';
            }
        }

        if ( $builder['limit'] !== null )
        {
            // Add limiting
            $query .= ' LIMIT ' . $builder['limit'];
        }

        if ( $builder['offset'] !== null )
        {
            // Add offsets
            $query .= ' OFFSET ' . $builder['offset'];
        }

        return $query;
    }

    /**
     * Compile the SQL query and return it.
     *
     * @return  string
     */
    protected function _compile_insert($builder, $type = 'INSERT')
    {
        if ( $type != 'REPLACE' )
        {
            $type = 'INSERT';
        }
        // Start an insertion query
        $query = $type . ' INTO ' . $this->quote_table($builder['table']);

        // Add the column names
        $query .= ' (' . implode(', ', array_map(array($this, '_quote_identifier'), $builder['columns'])) . ') ';

        if ( is_array($builder['values']) )
        {
            // Callback for quoting values
            $quote = array($this, 'quote');

            $groups = array();
            foreach ( $builder['values'] as $group )
            {
                foreach ( $group as $i => $value )
                {
                    if ( is_string($value) && isset($builder['parameters'][$value]) )
                    {
                        // Use the parameter value
                        $group[$i] = $builder['parameters'][$value];
                    }
                }

                $groups[] = '(' . implode(', ', array_map($quote, $group)) . ')';
            }

            // Add the values
            $query .= 'VALUES ' . implode(', ', $groups);
        }
        else
        {
            // Add the sub-query
            $query .= (string)$builder['values'];
        }

        if ( $type == 'REPLACE' )
        {
            //where
            if ( ! empty($builder['where']) )
            {
                // Add selection conditions
                $query .= ' WHERE ' . $this->_compile_conditions($builder['where'], $builder['parameters']);
            }
        }

        return $query;
    }

    protected function _compile_update($builder)
    {
        // Start an update query
        $query = 'UPDATE ' . $this->quote_table($builder['table']);

        // Add the columns to update
        $query .= ' SET ' . $this->_compile_set($builder['set'], $builder['parameters']);

        if ( ! empty($builder['where']) )
        {
            // Add selection conditions
            $query .= ' WHERE ' . $this->_compile_conditions($builder['where'], $builder['parameters']);
        }

        return $query;
    }

    protected function _compile_delete($builder)
    {
        // Start an update query
        $query = 'DELETE FROM' . $this->quote_table($builder['table']);

        if ( ! empty($builder['where']) )
        {
            // Add selection conditions
            $query .= ' WHERE ' . $this->_compile_conditions($builder['where'], $builder['parameters']);
        }

        return $query;
    }

    /**
     * Compiles an array of ORDER BY statements into an SQL partial.
     *
     * @param   object  Database instance
     * @param   array   sorting columns
     * @return  string
     */
    protected function _compile_order_by(array $columns)
    {
        $sort = array();
        foreach ( $columns as $group )
        {
            list ( $column, $direction ) = $group;

            if ( ! empty($direction) )
            {
                // Make the direction uppercase
                $direction = ' ' . strtoupper($direction);
            }

            $sort[] = $this->_quote_identifier($column) . $direction;
        }

        return 'ORDER BY ' . implode(', ', $sort);
    }

    /**
     * Compiles an array of conditions into an SQL partial. Used for WHERE
     * and HAVING.
     *
     * @param   object  Database instance
     * @param   array   condition statements
     * @return  string
     */
    protected function _compile_conditions(array $conditions, $parameters)
    {
        $last_condition = NULL;

        $sql = '';
        foreach ( $conditions as $group )
        {
            // Process groups of conditions
            foreach ( $group as $logic => $condition )
            {
                if ( $condition === '(' )
                {
                    if ( ! empty($sql) && $last_condition !== '(' )
                    {
                        // Include logic operator
                        $sql .= ' ' . $logic . ' ';
                    }

                    $sql .= '(';
                }
                elseif ( $condition === ')' )
                {
                    $sql .= ')';
                }
                else
                {
                    if ( ! empty($sql) && $last_condition !== '(' )
                    {
                        // Add the logic operator
                        $sql .= ' ' . $logic . ' ';
                    }

                    // Split the condition
                    list ( $column, $op, $value ) = $condition;

                    if ( $value === NULL )
                    {
                        if ( $op === '=' )
                        {
                            // Convert "val = NULL" to "val IS NULL"
                            $op = 'IS';
                        }
                        elseif ( $op === '!=' )
                        {
                            // Convert "val != NULL" to "valu IS NOT NULL"
                            $op = 'IS NOT';
                        }
                    }

                    // Database operators are always uppercase
                    $op = strtoupper($op);

                    if ( $op === 'BETWEEN' && is_array($value) )
                    {
                        // BETWEEN always has exactly two arguments
                        list ( $min, $max ) = $value;

                        if ( is_string($min) && array_key_exists($min, $parameters) )
                        {
                            // Set the parameter as the minimum
                            $min = $parameters[$min];
                        }

                        if ( is_string($max) && array_key_exists($max, $parameters) )
                        {
                            // Set the parameter as the maximum
                            $max = $parameters[$max];
                        }

                        // Quote the min and max value
                        $value = $this->quote($min) . ' AND ' . $this->quote($max);
                    }
                    else
                    {
                        if ( is_string($value) && array_key_exists($value, $parameters) )
                        {
                            // Set the parameter as the value
                            $value = $parameters[$value];
                        }

                        // Quote the entire value normally
                        $value = $this->quote($value);
                    }

                    // Append the statement to the query
                    $sql .= $this->_quote_identifier($column) . ' ' . $op . ' ' . $value;
                }

                $last_condition = $condition;
            }
        }

        return $sql;
    }

    /**
     * Compiles an array of JOIN statements into an SQL partial.
     *
     * @param   object  Database instance
     * @param   array   join statements
     * @return  string
     */
    protected function _compile_join(array $joins)
    {
        $statements = array();

        foreach ( $joins as $join )
        {
            $statements[] = $this->_compile_join_on($join);
        }

        return implode(' ', $statements);
    }

    protected function _compile_join_on($join)
    {
        if ( $join['type'] )
        {
            $sql = strtoupper($join['type']) . ' JOIN';
        }
        else
        {
            $sql = 'JOIN';
        }

        // Quote the table name that is being joined
        $sql .= ' ' . $this->quote_table($join['table']) . ' ON ';

        $conditions = array();
        foreach ( $join['on'] as $condition )
        {
            // Split the condition
            list ( $c1, $op, $c2 ) = $condition;

            if ( $op )
            {
                // Make the operator uppercase and spaced
                $op = ' ' . strtoupper($op);
            }

            // Quote each of the identifiers used for the condition
            $conditions[] = $this->_quote_identifier($c1) . $op . ' ' . $this->_quote_identifier($c2);
        }

        // Concat the conditions "... AND ..."
        $sql .= '(' . implode(' AND ', $conditions) . ')';

        return $sql;
    }

    /**
     * Compiles an array of set values into an SQL partial. Used for UPDATE.
     *
     * @param   object  Database instance
     * @param   array   updated values
     * @return  string
     */
    protected function _compile_set(array $values, $parameters)
    {
        $set = array();
        foreach ( $values as $group )
        {
            // Split the set
            list ( $column, $value , $op ) = $group;

            if ( $op=='+' || $op=='-' )
            {
                $w_type = $op;
            }
            else
            {
                $w_type = '';
            }

            // Quote the column name
            $column = $this->_quote_identifier($column);

            if ( is_string($value) and array_key_exists($value, $parameters) )
            {
                // Use the parameter value
                $value = $parameters[$value];
            }

            if ( $w_type )
            {
                $set[$column] = $column . ' = ' . $column . ' ' . $w_type . ' ' . $this->quote($value);
            }
            else
            {
                $set[$column] = $column . ' = ' . $this->quote($value);
            }
        }

        return implode(', ', $set);
    }

}